package cam.School.DataAccessObjectImpl;

import static cam.School.DataAccessObject.DAOUtil.*;
import cam.School.DAOException.DAOException;
import cam.School.DataAccessObject.DAOFactory;
import cam.School.DataAccessObject.UserDAO;
import cam.School.DataTransfertObject.UserDTO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * This class represents a concrete JDBC implementation of the {@link UserDAO}
 * interface.
 * 
 */
public class UserDAOImpl implements UserDAO {

	// Constants
	// ----------------------------------------------------------------------------------
	

	private static final String QUERY_PARAMS = "id, username, password, userPrivileg, firstname,"
			+ " lastname, street, city, country, email, cellPhone, workPhone, createdby,"
			+ " firstLogin, lastLogin";
	

	private static final String INSERT_PARAMS = "(username, password, isDeleted, userPrivileg, firstname,"
			+ " lastname, street, city, country, email, cellPhone, workPhone, createdby,"
			+ " firstLogin, lastLogin, isOnline)";

	private static final String UPDATE_QUERY_PARAMS = "pictureData = ?, username = ?, password = ?, firstname = ?,"
			+ " lastname = ?, street = ?, city = ?, country = ?, email = ?, cellPhone = ?, workPhone = ?, firstAccess = ?,"
			+ " lastAccess = ?, lastLogin = ?, currentLogin = ?";

	private static final String SQL_FIND_BY_ID = "SELECT " + QUERY_PARAMS
			+ " FROM user WHERE  id = ? AND isDeleted = 0";
	
	private static final String SQL_FIND_IMAGE_BY_USER_ID = "SELECT location " 
			+ " FROM Image WHERE  objectId = ?";
	
	private static final String SQL_FIND_BY_USERNAME_AND_PASSWORD = "SELECT "
			+ QUERY_PARAMS
			+ " FROM user WHERE username = ? AND password = MD5(?) AND isDeleted = 0 and isOnline = 0";
	
	private static final String SQL_UPDATE_BY_USERNAME_AND_PASSWORD = "UPDATE user SET "
			+ UPDATE_QUERY_PARAMS
			+ " WHERE username = ? AND password = MD5(?) AND isDeleted = 0";
	
	private static final String SQL_LOG_IN_OR_OUT = "UPDATE user SET isOnline = ?"
			+ " WHERE username = ? AND id = ? AND isDeleted = 0";

	private static final String SQL_LIST_ORDER_BY_ID = "SELECT "
			+ QUERY_PARAMS + " FROM user ORDER BY id";

	private static final String SQL_INSERT = "INSERT INTO user "+INSERT_PARAMS+" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	private static final String SQL_DELETE = "DELETE FROM user WHERE id = ?";

	private static final String SQL_EXIST_EMAIL = "SELECT id FROM user WHERE email = ?";

	private static final String SQL_CHANGE_PASSWORD = "UPDATE user SET password = MD5(?) WHERE id = ?";

	// Vars
	// ---------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	// Constructors
	// -------------------------------------------------------------------------------

	/**
	 * Construct an User DAO for the given DAOFactory. Package private so that
	 * it can be constructed inside the DAO package only.
	 * 
	 * @param daoFactory
	 *            The DAOFactory to construct this User DAO for.
	 */
	public UserDAOImpl(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// Actions
	// ------------------------------------------------------------------------------------

	public UserDTO find(Long id) throws DAOException {
		return find(SQL_FIND_BY_ID, id);
	}

	public UserDTO find(String username, String password) throws DAOException {
		UserDTO foundedUser = find(SQL_FIND_BY_USERNAME_AND_PASSWORD, username, password);
		//1: to login or 0: to logout
		if(foundedUser != null){
			Object[]params = {1, username, foundedUser.getId()};
			if(executeUpdateStatement(SQL_LOG_IN_OR_OUT, params) == 200){			
				return foundedUser;		
			}
		}		
		return null;
	}
	
	
	private String getUserImagePathLocation(long userId){
		return getUserImagePathLocation(SQL_FIND_IMAGE_BY_USER_ID, userId);		
	}

	private String getUserImagePathLocation(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				return resultSet.getString("location");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return "default Location"; //TO DO 
	}

	private UserDTO find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		UserDTO user = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				user = map(resultSet);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		
		return user;
	}

	@Override
	public List<UserDTO> list() throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<UserDTO> users = new ArrayList<UserDTO>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection
					.prepareStatement(SQL_LIST_ORDER_BY_ID);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				users.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return users;
	}

	@Override
	public long create(UserDTO user) throws IllegalArgumentException,
			DAOException {
		
		Object[] values = {
				user.getUsername(), 
				user.getPassword(), 
				false,
				user.getUserPrivileg(),
				user.getFirstname(), 
				user.getLastname(), 
				user.getStreet(),
				user.getCity(), 
				user.getCountry(), 
				user.getEmail(),
				user.getCellPhone(), 
				user.getWorkPhone(),
				user.getCreatedBy(),
				toSqlDate(user.getFirstLogin()),
				toSqlDate(user.getLastLogin()), 
				false
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_INSERT, false,
					values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				// DAOException("Creating user failed, no rows affected.");
				return 404;
			} else {
				return 200;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, generatedKeys);
		}
	}

	public int executeUpdateStatement(String sql, Object[] values) throws DAOException {
		

//		Object[] values = { user.getEmail(), user.getFirstname(),
//				user.getLastname(),
//				// toSqlDate(user.getBirthdate()),
//				user.getId() };

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int affectedRowCount;
		
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection,
					sql, false, values);
			affectedRowCount = preparedStatement.executeUpdate();
			
			if(affectedRowCount  == 1){
				return 200;
			}
			
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
		return 304;
	}

	public void delete(UserDTO user) throws DAOException {
		Object[] values = { user.getId() };

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_DELETE, false,
					values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException(
						"Deleting user failed, no rows affected.");
			} else {
				user.setId(0);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	public boolean existEmail(String email) throws DAOException {
		Object[] values = { email };

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean exist = false;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_EXIST_EMAIL,
					false, values);
			resultSet = preparedStatement.executeQuery();
			exist = resultSet.next();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return exist;
	}

	public void changePassword(UserDTO user) throws DAOException {
		

		Object[] values = { user.getPassword(), user.getId() };

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection,
					SQL_CHANGE_PASSWORD, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException(
						"Changing password failed, no rows affected.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	// Helpers
	// ------------------------------------------------------------------------------------

	/**
	 * Map the current row of the given ResultSet to an User.
	 * 
	 * @param resultSet
	 *            The ResultSet of which the current row is to be mapped to an
	 *            User.
	 * @return The mapped User from the current row of the given ResultSet.
	 * @throws SQLException
	 *             If something fails at database level.
	 */

	private UserDTO map(ResultSet resultSet)
			throws SQLException {
		UserDTO user = new UserDTO();
		user.setId(resultSet.getLong("id"));
		
		user.setUsername(resultSet.getString("username"));
		user.setPassword(resultSet.getString("password"));
		user.setUserPrivileg(resultSet.getString("userPrivileg"));
		user.setFirstname(resultSet.getString("firstname"));
		user.setLastname(resultSet.getString("lastname"));
		user.setStreet(resultSet.getString("street"));
		user.setCity(resultSet.getString("city"));
		user.setCountry(resultSet.getString("country"));
		user.setEmail(resultSet.getString("email"));
		user.setCellPhone(resultSet.getString("cellPhone"));		
		user.setCreatedBy(resultSet.getString("createdby"));
		user.setWorkPhone(resultSet.getString("workPhone"));
		user.setFirstLogin(resultSet.getDate("firstLogin"));
		user.setLastLogin(resultSet.getDate("lastLogin"));
		user.setPictureLocationPath(getUserImagePathLocation(user.getId()));		
		return user;
	}

	@Override
	public void update(UserDTO user) throws IllegalArgumentException,
			DAOException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public int update(String username, long id) throws DAOException {
		Object []params = {0, username, id};
		return executeUpdateStatement(SQL_LOG_IN_OR_OUT, params);
	}
	
}
